1% completed
Problem Statement
Table: Posts
Each row in this table represents a post made by a user in a social media platform. It contains the unique post ID, the user who posted it, and the number of likes received by that post.
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| post_id | int |
| user_id | int |
| post_likes | int |
+-------------+---------+
(post_id, user_id)is a unique primary key in the above table.post_idis the primary key for this table.user_idrefers to the ID of the user who has liked the post.
Develop a solution to find the number of likes for each post.
Return the result table ordered by post_id in ascending order.
Example
Input:
Posts table: +---------+---------+ | post_id | user_id | +---------+---------+ | 1 | 2 | | 2 | 1 | | 1 | 3 | | 3 | 1 | | 1 | 4 | | 4 | 2 | | 2 | 2 | | 4 | 3 | +---------+---------+
Output:
+---------+-------------+ | post_id | post_likes | +---------+-------------+ | 1 | 3 | | 2 | 2 | | 3 | 1 | | 4 | 2 | +---------+-------------+
Try It Yourself
Posts =
Solution
To determine the number of likes each post has received, we analyze the Posts table. Each row in the Posts table represents a like made by a user (user_id) on a specific post (post_id). By aggregating this data, we can count how many users have liked each post, effectively determining the total number of likes per post.
Here are the steps for the solution.
-
Aggregate Likes per Post:
- Count the number of
user_ids associated with eachpost_idto determine the total likes per post.
- Count the number of
-
Order the Results:
- Present the final count of likes in ascending order of
post_idfor clarity and organization.
- Present the final count of likes in ascending order of
SQL Query
SELECT post_id, COUNT(user_id) AS post_likes FROM Posts GROUP BY post_id ORDER BY post_id ASC;
Step-by-Step Approach
Step 1: Select Relevant Columns
Identify the columns necessary for calculating the number of likes per post.
SQL Snippet:
SELECT post_id, COUNT(user_id) AS post_likes
Explanation:
-
SELECT post_id,- *Retrieve the
post_idto group likes per post.
- *Retrieve the
-
COUNT(user_id) AS post_likesCOUNT(user_id)counts the number of non-NULLuser_identries for eachpost_id.AS post_likesaliases the count aspost_likesfor clarity in the output.
Step 2: Aggregate Data Using GROUP BY
Group the data by post_id to perform aggregation on a per-post basis.
SQL Snippet:
GROUP BY post_id
Explanation:
- Groups all rows that have the same
post_idinto a single group. - The
COUNT(user_id)function then operates within each group to count the number of likes.
Intermediate Output After Step 3:
+---------+-------------+ | post_id | post_likes | +---------+-------------+ | 1 | 3 | | 2 | 2 | | 3 | 1 | | 4 | 2 | +---------+-------------+
Step 3: Order the Results
Sort the final results by post_id in ascending order for organized presentation.
SQL Snippet:
ORDER BY post_id ASC;
Explanation:
ORDER BY post_id ASC;ORDER BY post_idspecifies the column to sort by.ASCdenotes ascending order (from smallest to largest).
Final Output:
+---------+-------------+ | post_id | post_likes | +---------+-------------+ | 1 | 3 | | 2 | 2 | | 3 | 1 | | 4 | 2 | +---------+-------------+
On this page
Problem Statement
Example
Try It Yourself
Solution
SQL Query
Step-by-Step Approach